import pymysql

class DButils:
    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self.con = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            port=port,
            charset=charset
        )


    def get_cursor(self):
        # 每次调用时重新获取一个新的游标
        return self.con.cursor()

    def close(self):
        # 关闭数据库连接
        self.con.close()

    def insert(self, sql, params=None):
        try:
            cursor = self.get_cursor()
            cursor.execute(sql, params)
            self.con.commit()
            print('数据插入成功')
        except Exception as e:
            print('数据更新失败', e)
            self.con.rollback()  # 发生异常时回滚事务
        finally:
            cursor.close()  # 确保游标被关闭


# 使用示例
if __name__ == '__main__':
    db = DButils('127.0.0.1', 'root', 'root', 'class_three')

    # 假设我们要插入的数据和 SQL 语句如下
    people = [
        ('李四', 20, '女', 1.60),
        ('张起', 21, '男', 1.75),
        ('李六', 19, '女', 1.80),
        ('李八', 22, '男', 1.90),
        ('王五', 25, '男', 1.70),
        ('赵七', 23, '女', 1.65),
        ('孙九', 24, '女', 1.68),
        ('周十', 26, '男', 1.82),
        ('吴十一', 27, '男', 1.78),
        ('郑十二', 28, '女', 1.72)
        # 添加更多数据...
    ]
    insert_sql = "INSERT INTO person (username, age, sex, high) VALUES (%s, %s, %s, %s)"

    for person in people:
        db.insert(insert_sql, person)

    db.close()